#!/use/bin/python
# coding=utf-8
# 主动服务 介绍页/设置页转化率
import datetime
import pymysql
from datetime import date, timedelta

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 主动服务-早间开机介绍页/设置页转化率 analysis_conversion_rate
def insertZjkjConversionRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当天 早间开机转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='zjkj' and t.ref_date =  '%s' 
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日进入介绍页次数 且早间服务开关为关闭的
        selectShowSql = """
                    select count(DISTINCT(t.active_id))   from log_common_showpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and zjkj = 0
                """% date;
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当日从介绍页打开早间服务的次数
        selectShowOpenCountSql = """
                    select count(DISTINCT(t.active_id))  from log_common_openser t where t.from ='introductionPage' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'ZJKJ'
                """ % date
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当日进入设置次数 且早间服务开关为关闭的
        selectSettingSql = """
                    select count(DISTINCT(t.active_id))   from log_common_settingpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and zjkj = 0
                """% date;
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当日从设置页打开早间服务的次数
        selectSettingOpenCountSql = """
                    select count(DISTINCT(t.active_id))  from log_common_openser t where t.from ='setting' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'ZJKJ'
                """ % date
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s日新增-早间开机-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入早间服务转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'ZJKJ','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-早间开机-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新早间服务订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='ZJKJ' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-夜间音量介绍页/设置页转化率 analysis_conversion_rate
def insertYjylConversionRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当天 夜间音量转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='yjyl' and t.ref_date =  '%s' 
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日进入介绍页次数 且夜间音量开关为关闭的
        selectShowSql = """
                    select count(DISTINCT(t.active_id))  from log_common_showpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and yjyl = 0
                """% date;
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当日从介绍页打开夜间音量的次数
        selectShowOpenCountSql = """
                    select count(DISTINCT(t.active_id)) from log_common_openser t where t.from ='introductionPage' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'YJYL'
                """ % date
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当日进入设置次数 且夜间音量开关为关闭的
        selectSettingSql = """
                    select count(DISTINCT(t.active_id))  from log_common_settingpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and yjyl = 0
                """% date;
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当日从设置页打开早间服务的次数
        selectSettingOpenCountSql = """
                    select count(DISTINCT(t.active_id)) from log_common_openser t where t.from ='setting' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'YJYL'
                """ % date
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s日新增-夜间音量-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入夜间音量转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'YJYL','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-夜间音量-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新夜间音量订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='YJYL' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-健康作息介绍页/设置页转化率 analysis_conversion_rate
def insertJkzxConversionRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当天 健康作息转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='jkzx' and t.ref_date =  '%s' 
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日进入介绍页次数 且健康作息开关为关闭的
        selectShowSql = """
                    select count(DISTINCT(t.active_id))  from log_common_showpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and jkzx = 0
                """% date;
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当日从介绍页打开健康作息的次数
        selectShowOpenCountSql = """
                    select count(DISTINCT(t.active_id)) from log_common_openser t where t.from ='introductionPage' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'JKZX'
                """ % date
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当日进入设置次数 且健康作息开关为关闭的
        selectSettingSql = """
                    select count(DISTINCT(t.active_id))  from log_common_settingpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and jkzx = 0
                """% date;
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当日从设置页打开健康作息的次数
        selectSettingOpenCountSql = """
                    select count(DISTINCT(t.active_id)) from log_common_openser t where t.from ='setting' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'JKZX'
                """ % date
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s日新增-健康作息-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入健康作息转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'JKZX','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-健康作息-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新健康作息订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='JKZX' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-追剧提醒介绍页/设置页转化率 analysis_conversion_rate
def insertZjtxConversionRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当天 追剧提醒转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='zjtx' and t.ref_date =  '%s' 
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日进入介绍页次数 且追剧提醒开关为关闭的
        selectShowSql = """
                    select count(DISTINCT(t.active_id))  from log_common_showpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and zjtx = 0
                """% date;
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当日从介绍页打开追剧提醒的次数
        selectShowOpenCountSql = """
                    select count(DISTINCT(t.active_id)) from log_common_openser t where t.from ='introductionPage' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'ZJTX'
                """ % date
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当日进入设置次数 且追剧提醒开关为关闭的
        selectSettingSql = """
                    select count(DISTINCT(t.active_id))  from log_common_settingpage t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and zjtx = 0
                """% date;
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当日从设置页打开追剧提醒的次数
        selectSettingOpenCountSql = """
                    select count(DISTINCT(t.active_id)) from log_common_openser t where t.from ='setting' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and serId = 'ZJTX'
                """ % date
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s日新增-追剧提醒-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入追剧提醒转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'ZJTX','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-追剧提醒-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                date, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新追剧提醒订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='ZJTX' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-早间开机介绍页/设置页转化率 analysis_conversion_rate -- 当月
def insertZjkjConversionRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当月 早间开机转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='zjkj' and t.ref_date =  '%s' 
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当月日进入介绍页次数 且早间服务开关为关闭的
        selectShowSql = """
                    select sum(introduction_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjkj'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当约从介绍页打开早间服务的次数
        selectShowOpenCountSql = """
                    select sum(introduction_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjkj'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当月进入设置次数 且早间服务开关为关闭的
        selectSettingSql = """
                    select sum(setting_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjkj'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当月从设置页打开早间服务的次数
        selectSettingOpenCountSql = """
                    select sum(setting_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjkj'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s月新增-早间开机-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入早间服务转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'ZJKJ','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月更新-早间开机-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新早间服务订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='ZJKJ' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-夜间音量介绍页/设置页转化率 analysis_conversion_rate -- 当月
def insertYjylConversionRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当月 夜间音量转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='yjyl' and t.ref_date =  '%s' 
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当月日进入介绍页次数 且夜间音量开关为关闭的
        selectShowSql = """
                    select sum(introduction_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='yjyl'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当约从介绍页打开夜间音量的次数
        selectShowOpenCountSql = """
                    select sum(introduction_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='yjyl'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当月进入设置次数 且夜间音量开关为关闭的
        selectSettingSql = """
                    select sum(setting_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='yjyl'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当月从设置页打开夜间音量的次数
        selectSettingOpenCountSql = """
                    select sum(setting_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='yjyl'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s月新增-夜间音量-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入夜间音量转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'YJYL','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月更新-夜间音量-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新夜间音量订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='YJYL' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-健康作息介绍页/设置页转化率 analysis_conversion_rate -- 当月
def insertJkzxConversionRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当月 健康作息转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='jkzx' and t.ref_date =  '%s' 
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当月日进入介绍页次数 且健康作息开关为关闭的
        selectShowSql = """
                    select sum(introduction_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='jkzx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当约从介绍页打开健康作息的次数
        selectShowOpenCountSql = """
                    select sum(introduction_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='jkzx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当月进入设置次数 且健康作息开关为关闭的
        selectSettingSql = """
                    select sum(setting_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='jkzx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当月从设置页打开健康作息的次数
        selectSettingOpenCountSql = """
                    select sum(setting_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='jkzx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s月新增-健康作息-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入健康作息转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'JKZX','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月更新-健康作息-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新健康作息订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='JKZX' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-追剧提醒介绍页/设置页转化率 analysis_conversion_rate -- 当月
def insertZjtxConversionRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_conversion_rate 表是否已经存在当月 追剧提醒转化率的数据
        selectCountSql = """
                    select count(1) num from analysis_conversion_rate t where t.service_key ='zjtx' and t.ref_date =  '%s' 
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当月日进入介绍页次数 且追剧提醒开关为关闭的
        selectShowSql = """
                    select sum(introduction_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjtx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowSql)
        resultShowNum = cur.fetchone()
        # 获取当约从介绍页打开追剧提醒的次数
        selectShowOpenCountSql = """
                    select sum(introduction_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjtx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectShowOpenCountSql)
        resultShowOpenNum = cur.fetchone()
        # 获取当月进入设置次数 且追剧提醒开关为关闭的
        selectSettingSql = """
                    select sum(setting_show_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjtx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingSql)
        resultSettingNum = cur.fetchone()
        # 获取当月从设置页打开追剧提醒的次数
        selectSettingOpenCountSql = """
                    select sum(setting_open_num)  from analysis_conversion_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' and t.service_key ='zjtx'
                """% (toMonthDay,toMonthDay);
        cur.execute(selectSettingOpenCountSql)
        resultSettingOpenNum = cur.fetchone()
        showNum = resultShowNum[0]
        showOpenNum = resultShowOpenNum[0]
        settingNum = resultSettingNum[0]
        settingOpenNum = resultSettingOpenNum[0]
        showOpenRate = 0
        settingOpenRate = 0
        if showNum != 0:
            if showOpenNum > showNum:
                showOpenRate = 100
            else:
                showOpenRate = round(showOpenNum / showNum, 4) * 100

        if settingNum != 0:
            if settingOpenNum > settingNum:
                settingOpenRate = 100
            else:
                settingOpenRate = round(settingOpenNum / settingNum, 4) * 100

        if countNum == 0:
            logDetail = "%s月新增-追剧提醒-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay,showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate)
            print(logDetail)
            # 插入追剧提醒转化率
            insertSql = """insert into analysis_conversion_rate(introduction_show_num,introduction_open_num,introduction_conver_rate,setting_show_num,
                    setting_open_num,setting_conver_rate,service_key,ref_date) 
                value(%s,%s,%s,%s,%s,%s,'ZJTX','%s')""" % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月更新-追剧提醒-进入介绍页次数%s,进入介绍页并打开服务次数%s,介绍页转化率%s,进入设置页次数%s,进入设置页并打开服务次数%s,设置页转化率%s"% (
                toMonthDay, showNum, showOpenNum, showOpenRate, settingNum, settingOpenNum, settingOpenRate)
            print(logDetail)
            # 更新追剧提醒订阅设备量
            updateSql = """
                update analysis_conversion_rate set introduction_show_num = %s ,introduction_open_num = %s ,introduction_conver_rate = %s ,setting_show_num = %s ,
                    setting_open_num = %s ,setting_conver_rate = %s ,update_time = now()  where
                    ref_date = '%s' and service_key ='ZJTX' 
            """ % (showNum,showOpenNum,showOpenRate,settingNum,settingOpenNum,settingOpenRate,toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 51
        jobName = '核心指标-生活助手-介绍页/设置页转化率'
        tableName = 'analysis_conversion_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    beforeDate = getdate(2)
    monthdate = getMonthdate(1)
    #today = "2021-12-13"
    print ("%s 日期,执行<主动服务转化率>报表" % date)
    insertZjkjConversionRate(date)
    insertYjylConversionRate(date)
    insertJkzxConversionRate(date)
    insertZjtxConversionRate(date)
    insertZjkjConversionRateMonth(monthdate)
    insertYjylConversionRateMonth(monthdate)
    insertJkzxConversionRateMonth(monthdate)
    insertZjtxConversionRateMonth(monthdate)
